package com.navi.loaddata.loadhis;

import com.navi.loaddata.core.entity.ProductWipHis;
import com.navi.loaddata.core.gpEntity.*;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MySqlRepositoryImpl;
import com.navi.loaddata.utils.JacksonUtil;
import com.navi.loaddata.utils.JdbcUtil;
import com.navi.loaddata.utils.SaveEtlPoint;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.amqp.core.AmqpTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisPool;
import redis.clients.jedis.Pipeline;

import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager")
public class LoadProductWipHis {

    private static final Logger logger = LoggerFactory.getLogger(LoadProductWipHis.class);
    @Autowired
    private GreenPlumRepositoryImpl gpDao;

    @Autowired
    private MySqlRepositoryImpl mysqlDao;

    @Autowired
    private AmqpTemplate rabbitTemplate;

    @Autowired
    private SaveEtlPoint saveEtlPoint;

    @Autowired
    private JedisPool jedisPool;

    @Value("${etl.loadprodstart}")
    private String startTime;
    @Value("${etl.loadprodend}")
    private String endTime;


    //@Scheduled(cron = "0 0/30 * * * ?")
    // @Scheduled(fixedDelay = 1000 * 60 * 2)
    public void loadprodHis() {
        LocalDateTime etlStart = LocalDateTime.now();
        try {
            Timestamp sysTimestamp = null;
            Object queryObj = gpDao.getEntityManager().createNativeQuery("SELECT max(mes_timestamp) FROM wpp_sht_info").getSingleResult();
            if (queryObj != null) {
                sysTimestamp = (Timestamp) queryObj;
                logger.info("sht_info时间为"+sysTimestamp);
            }
            QueryRunner qr_ora = JdbcUtil.getQueryRunner_ora();
            String sql = "";
            if (sysTimestamp != null) {
                sql = "SELECT" +
                        " FACTORYNAME AS factory_name," +
                        " SUBFACTORYNAME AS subfactory_name," +
                        " ARRAYGLASSNAME AS TFT_GLASS_ID ," +
                        " CFGLASSNAME AS CF_GLASS_ID," +
                        " PRODUCTNAME AS GLASS_ID," +
                        " ARRAYPRODUCTSPECNAME AS TFT_PROD_ID," +
                        " CFPRODUCTNAME AS CF_PROD_ID," +
                        " PRODUCTSPECNAME AS PROD_ID," +
                        " OWNERID AS OWNER," +
                        " EVENTUSER AS EVT_USER," +
                        " INSPTARGETEQP AS UNIT_EQ," +
                        " PRODUCTGRADE AS grade," +
                        " OLDPROCESSOPERATIONNAME AS OPE_ID," +
                        " MACHINENAME AS EQPT_ID," +
                        " MACHINERECIPENAME AS RECIPE_ID," +
                        " OLDLOTNAME AS lot_id," +
                        " POSITION," +
                        " PRODUCTSPECVERSION AS PRODUCT_SPECVERSION," +
                        " CARRIERNAME AS CARRIER_NAME," +
                        " REWORKCOUNT AS REWORK_COUNT," +
                        " SYSTEMTIME AS MES_TIMESTAMP," +
                        " EVENTTIME AS EVENT_TIMESTAMP," +
                        " EVENTNAME AS EVENT_NAME" +
                        " FROM " +
                        "  MES_PRODUCTHISTORY" +
                        " WHERE " +
                        " EVENTNAME IN('TrackOut','TrackIn')" +
                        "  AND SYSTEMTIME >= to_date('" + sysTimestamp.toString().substring(0, 19) + "','yyyy/mm/dd HH24:MI:SS')" +
                        "  AND SYSTEMTIME < to_date('" + Timestamp.valueOf(LocalDateTime.now()).toString().substring(0, 19) + "','yyyy/mm/dd HH24:MI:SS')" +
                        " ORDER BY SYSTEMTIME";
            } else {
               // Object[] query = qr_ora.query("SELECT min(SYSTEMTIME) FROM MES_PRODUCTHISTORY", new ArrayHandler());

                //Timestamp mesMinTime = (Timestamp) query[0];
                sql = "SELECT" +
                        " FACTORYNAME AS factory_name," +
                        " SUBFACTORYNAME AS subfactory_name," +
                        " ARRAYGLASSNAME AS TFT_GLASS_ID ," +
                        " CFGLASSNAME AS CF_GLASS_ID," +
                        " PRODUCTNAME AS GLASS_ID," +
                        " ARRAYPRODUCTSPECNAME AS TFT_PROD_ID," +
                        " CFPRODUCTNAME AS CF_PROD_ID," +
                        " PRODUCTSPECNAME AS PROD_ID," +
                        " OWNERID AS OWNER," +
                        " EVENTUSER AS EVT_USER," +
                        " INSPTARGETEQP AS UNIT_EQ," +
                        " PRODUCTGRADE AS grade," +
                        " OLDPROCESSOPERATIONNAME AS OPE_ID," +
                        " MACHINENAME AS EQPT_ID," +
                        " MACHINERECIPENAME AS RECIPE_ID," +
                        " OLDLOTNAME AS lot_id," +
                        " POSITION," +
                        " PRODUCTSPECVERSION AS PRODUCT_SPECVERSION," +
                        " CARRIERNAME AS CARRIER_NAME," +
                        " REWORKCOUNT AS REWORK_COUNT," +
                        " SYSTEMTIME AS MES_TIMESTAMP," +
                        " EVENTTIME AS EVENT_TIMESTAMP," +
                        " EVENTNAME AS EVENT_NAME" +
                        " FROM " +
                        "  MES_PRODUCTHISTORY" +
                        " WHERE " +
                        " EVENTNAME IN('TrackOut','TrackIn')" +
                        //"  AND SYSTEMTIME >= to_date('" + mesMinTime.toString().substring(0, 19) + "','yyyy/mm/dd HH24:MI:SS')" +
                        "  AND SYSTEMTIME <= to_date('" +  Timestamp.valueOf(LocalDateTime.now()).toString().substring(0, 19) + "','yyyy/mm/dd HH24:MI:SS')" +
                        " ORDER BY SYSTEMTIME";
            }

            if (!("null").equals(startTime) || !("null").equals(endTime)) {
                logger.info("开始补数据时间", startTime);
                logger.info("结束补数据时间", endTime);
                sql = "SELECT" +
                        "  TrackOut.FACTORYNAME AS factory_name," +
                        "  TrackOut.SUBFACTORYNAME AS subfactory_name," +
                        "  TrackOut.ARRAYGLASSNAME AS TFT_GLASS_ID ," +
                        "  TrackOut.CFGLASSNAME AS CF_GLASS_ID," +
                        "  TrackOut.PRODUCTNAME AS GLASS_ID," +
                        "  TrackOut.PRODUCTSPECNAME AS PROD_ID," +
                        "  TrackOut.OWNERID AS OWNER," +
                        "  TrackOut.EVENTUSER AS EVT_USER," +
                        "  TrackOut.INSPTARGETEQP AS UNIT_EQ," +
                        "  TrackOut.PRODUCTGRADE AS grade," +
                        "  TrackOut.OLDPROCESSOPERATIONNAME AS OPE_ID," +
                        "  TrackOut.MACHINENAME AS EQPT_ID," +
                        "  TrackOut.MACHINERECIPENAME AS RECIPE_ID," +
                        "  TrackOut.OLDLOTNAME AS lot_id," +
                        "  TrackOut.POSITION," +
                        "  TrackOut.PRODUCTSPECVERSION AS PRODUCT_SPECVERSION," +
                        "  TrackOut.CARRIERNAME AS CARRIER_NAME," +
                        "  TrackOut.REWORKCOUNT AS REWORK_COUNT," +
                        "  TrackOut.SYSTEMTIME AS MES_TIMESTAMP," +
                        "  TrackOut.EVENTTIME AS LOGOF_TIMESTAMP," +
                        "  TrackIn.EVENTTIME AS LOGON_TIMESTAMP" +
                        " FROM " +
                        "  MES_PRODUCTHISTORY TrackOut" +
                        "  INNER JOIN" +
                        "  MES_PRODUCTHISTORY TrackIn" +
                        "    ON TrackOut.SUBFACTORYNAME = TrackIn.SUBFACTORYNAME" +
                        "       AND TrackOut.PRODUCTNAME = TrackIn.PRODUCTNAME" +
                        "       AND TrackOut.OLDPROCESSOPERATIONNAME = TrackIn.OLDPROCESSOPERATIONNAME" +
                        "       AND TrackOut.MACHINENAME = TrackIn.MACHINENAME" +
                        "       AND TrackOut.REWORKCOUNT = TrackIn.REWORKCOUNT" +
                        "       AND TrackOut.LINKKEY = TrackIn.LINKKEY" +
                        " WHERE " +
                        "  TrackOut.EVENTNAME='TrackOut'" +
                        "  AND TrackIn.EVENTNAME='TrackIn'" +
                        "  AND TrackOut.SYSTEMTIME >= to_date('" + startTime.substring(0, 19) + "','yyyy/mm/dd HH24:MI:SS')" +
                        "  AND TrackOut.SYSTEMTIME <= to_date('" + endTime.substring(0, 19) + "','yyyy/mm/dd HH24:MI:SS')";
            }

            List<ProductWipHis> productHisList = qr_ora.query(sql, new BeanListHandler<ProductWipHis>(ProductWipHis.class));
            if (productHisList == null || productHisList.size() <= 0) return;
            //启动加载ope_conf
            List<WppShtOpeConf> opeConfList = gpDao.list(WppShtOpeConf.class, "from WppShtOpeConf where valigFlg = 'Y'");
            HashMap<String, String> opeConfMap = new HashMap<>();
            opeConfList.forEach(ope -> {
                String opeId = ope.getOpeId();
                String targetSeq = ope.getTargetSeq();
                opeConfMap.put(opeId, targetSeq);
            });
            //启动加载ope_conf_ext
            List<WppShtOpeConfExt> opeConfExtList = gpDao.list(WppShtOpeConfExt.class, "from WppShtOpeConfExt where valigFlg = 'Y'");
            HashMap<String, String> opeConfExtMap = new HashMap<>();
            opeConfExtList.forEach(ope -> {
                String opeId = ope.getOpeId();
                String targetColumn = ope.getTargetColumn();
                String eventName = ope.getEventName();
                String sourceColumn = ope.getSourceColumn();
                opeConfExtMap.put(opeId + "@" + targetColumn, eventName + "@" + sourceColumn);
            });
            productHisList.stream().forEach(productHis -> {
                loadProdWipHis(productHis, opeConfMap, opeConfExtMap, qr_ora);
            });
            saveEtlPoint.save2etlProductInfo(etlStart, productHisList);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private void loadProdWipHis(ProductWipHis productHis, HashMap<String, String> opeConfMap, HashMap<String, String> opeConfExtMap, QueryRunner qr_ora) {
        String prod_id = productHis.getProd_id();
        String glass_id = productHis.getGlass_id();
        //先走一波Redis
        String lot_id = productHis.getLot_id();
        String ope_id = productHis.getOpe_id();
        String eqpt_id = productHis.getEqpt_id();
        String recipe_id = productHis.getRecipe_id();
        Timestamp eventtime = productHis.getEvent_timestamp();
        long epochSecond = eventtime.toLocalDateTime().atZone(ZoneId.systemDefault()).toInstant().getEpochSecond();
        Double score = Double.valueOf(epochSecond);
        String redisValue = String.format("%s:%s:%s:%s:%s:%s", eqpt_id, eqpt_id, recipe_id, prod_id, lot_id, glass_id);
        final String strTime = eventtime.toLocalDateTime().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"));
        try (Jedis jedis = jedisPool.getResource()) {
            final Pipeline pipeline = jedis.pipelined();
            pipeline.multi();
            pipeline.zadd("history:" + ope_id, score, redisValue + ":M");
            pipeline.hset(score + glass_id, ope_id, strTime);
            pipeline.exec();
        }



        //基础表

        //判断产品有几片panel
        int panelNum = 0;
        try {
            BigDecimal panelNums = (BigDecimal) qr_ora.query("SELECT PRODUCTQUANTITY FROM MES_PRODUCTSPEC WHERE PRODUCTSPECNAME = ?", new ArrayHandler(), prod_id)[0];
            panelNum = panelNums.intValue();
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (panelNum == 0) {
            return;
        }
        //将glass 赋值01 02 ... 变成panel

        for (int i = 1; i < panelNum + 1; i++) {
            String panelId = String.format(glass_id + "%02d", i);

            try {
                //存入基础表
                saveShtInfo(productHis, panelId);
                //查询ope_conf 存入ope_info
                opeConfMap.forEach((opeId, tarSeq) -> {
                    try {
                        saveShtOpeInfo(productHis, panelId, opeId, tarSeq);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                });
                //查询ope_conf_ext 存入 ope_info_ext
                opeConfExtMap.forEach((opeWithTar, columnDesc) -> {
                    try {
                        saveShtInfoExt(productHis, panelId, opeWithTar, columnDesc);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                });
            } catch (Exception e) {
                e.printStackTrace();
            }


        }

    }

    private void saveShtInfoExt(ProductWipHis productHis, String panelId, String opeWithTar, String descWithSour) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
        //根据TARGET_COLUME 的source值给 ope_info_ext对应字段赋值
        String[] opeAndTar = opeWithTar.split("@");
        String opeId = opeAndTar[0];
        String tarSeq = opeAndTar[1];
        String[] descAndSour = descWithSour.split("@");
        String eventName = descAndSour[0];
        String sourceColumn = descAndSour[1];
        if (!opeId.equals(productHis.getOpe_id())) return;
        if (eventName.equals(productHis.getEvent_name())) {
            WppShtOpeInfoExt wppShtOpeInfoExt = gpDao.get(WppShtOpeInfoExt.class, panelId);
            if (wppShtOpeInfoExt == null) {
                wppShtOpeInfoExt = new WppShtOpeInfoExt();
            }
            wppShtOpeInfoExt.setPanelId(panelId);
            String sourceValue = BeanUtils.getProperty(productHis, sourceColumn);
            BeanUtils.setProperty(wppShtOpeInfoExt, tarSeq, sourceValue);
            wppShtOpeInfoExt.setOpeTblName("wpp_sht_ope_info_ext");
            rabbitTemplate.convertAndSend("qmsExchange", "wpp_sht_ope_info_ext", JacksonUtil.toJSONStr(wppShtOpeInfoExt));
            //gpDao.update(wppShtOpeInfoExt);
        }


    }

    private void saveShtOpeInfo(ProductWipHis productHis, String panelId, String opeId, String tarSeq) throws InvocationTargetException, IllegalAccessException {
         //根据tarSeq给 四个相应定字段赋值
        if (!opeId.equals(productHis.getOpe_id())) return;
        WppShtOpeInfo wppShtOpeInfo = gpDao.get(WppShtOpeInfo.class, panelId);
        if (wppShtOpeInfo == null) {
            wppShtOpeInfo = new WppShtOpeInfo();
        }
        wppShtOpeInfo.setPanelId(panelId);
        Integer tarCol = Integer.valueOf(tarSeq);
        String eqptFormat = String.format("eqptId%03d", tarCol);
        BeanUtils.setProperty(wppShtOpeInfo, eqptFormat, productHis.getEqpt_id());
        if ("TrackIn".equals(productHis.getEvent_name())) {
            String moveInFormat = String.format("moveInTime%03d", tarCol);
            BeanUtils.setProperty(wppShtOpeInfo, moveInFormat, productHis.getEvent_timestamp());
        }
        if ("TrackOut".equals(productHis.getEvent_name())) {
            String moveOutFormat = String.format("moveOutTime%03d", tarCol);
            BeanUtils.setProperty(wppShtOpeInfo, moveOutFormat, productHis.getEvent_timestamp());
        }
        String recipeFormat = String.format("recipeId%03d", tarCol);
        BeanUtils.setProperty(wppShtOpeInfo, recipeFormat, productHis.getRecipe_id());
        wppShtOpeInfo.setOpeTblName("wpp_sht_ope_info");
        rabbitTemplate.convertAndSend("qmsExchange", "wpp_sht_ope_info", JacksonUtil.toJSONStr(wppShtOpeInfo));
        //gpDao.update(wppShtOpeInfo);
    }


    private void saveShtInfo(ProductWipHis productHis, String panelId) {
        WppShtInfo wppShtInfo = new WppShtInfo();
        wppShtInfo.setPanelId(panelId);
        wppShtInfo.setTftGlassId(productHis.getTft_glass_id());
        wppShtInfo.setCfGlassId(productHis.getCf_glass_id());
        wppShtInfo.setLotId(productHis.getLot_id());
        wppShtInfo.setTftProdId(productHis.getTft_prod_id());
        wppShtInfo.setCfProdId(productHis.getCf_prod_id());
        wppShtInfo.setPfcd(productHis.getGlass_id());
            /*wppShtInfo.setGroupId();
            wppShtInfo.setSn();
            wppShtInfo.setTftPnlGrade();
            wppShtInfo.setCfPnlGrade();
            wppShtInfo.setOcPnlGrade();
            wppShtInfo.setTftStartTimestamp();
            wppShtInfo.setTftShippingTimestamp();
            wppShtInfo.setCfStartTimestamp();
            wppShtInfo.setCfShippingTimestamp();
            wppShtInfo.setOcStartTimestamp();
            wppShtInfo.setOcShippingTimestamp();*/
        wppShtInfo.setEtlTimestamp(Timestamp.valueOf(LocalDateTime.now()));
        wppShtInfo.setEvtTimestamp(productHis.getEvent_timestamp());
        wppShtInfo.setMesTimestamp(productHis.getMes_timestamp());
        wppShtInfo.setOpeTblName("wpp_sht_info");
        rabbitTemplate.convertAndSend("qmsExchange", "wpp_sht_info", JacksonUtil.toJSONStr(wppShtInfo));
        //gpDao.update(wppShtInfo);
    }



}
